﻿-- Full installation script
-- pre-deploy - must allow CLR for the instance

EXECUTE sp_configure 'clr enabled', 1;
GO
RECONFIGURE;
GO

--Deploy to a specific database, or if deployed to master
-- will be available to all databases on the instance
-- GO
USE [master] 
go

-- optionally drop existing objects
 DROP PROCEDURE [dbo].[ExecuteForEachDB];
 GO
 DROP PROCEDURE [dbo].[ExecuteForEachTable];
 GO
 DROP ASSEMBLY [SqlIterators];
 
 -- now catalog the assembly
CREATE ASSEMBLY [SqlIterators]
AUTHORIZATION [dbo]
FROM 'C:\SqlIterators\SqlIterators.dll'
WITH PERMISSION_SET = SAFE ;

GO

-- and catalog the stored procedures
CREATE PROCEDURE [dbo].[ExecuteForEachDB]
	@command1 NVARCHAR (4000)=N''				--required parameter
	, @replacechar NVARCHAR (1)=N'?'			--optional parameter
	, @command2 NVARCHAR (4000)=N''				--optional parameter
	, @command3 NVARCHAR (4000)=N''				--optional parameter
	, @precommand NVARCHAR (4000)=N''			--optional parameter
	, @postcommand NVARCHAR (4000)=N''			--optional parameter
	, @setnocounton BIT = true					--optional parameter
	, @messageOptions NVARCHAR (8) =N'Errora'	--optional parameter
WITH EXECUTE AS CALLER
AS EXTERNAL NAME [SqlIterators].[SqlIterators.MSForEachReplacements].[ExecuteForEachDB]
GO


CREATE PROCEDURE [dbo].[ExecuteForEachTable]
	@command1 NVARCHAR (4000)=N''				--required parameter
	, @database NVARCHAR (128)=N''				--required parameter
	, @replacechar NVARCHAR (1)=N'?'			--optional parameter
	, @command2 NVARCHAR (4000)=N''				--optional parameter
	, @command3 NVARCHAR (4000)=N''				--optional parameter
	, @precommand NVARCHAR (4000)=N''			--optional parameter
	, @postcommand NVARCHAR (4000)=N''			--optional parameter
	, @setnocounton BIT = true					--optional parameter
	, @messageOptions NVARCHAR (8) =N'Errora'	--optional parameter
WITH EXECUTE AS CALLER
AS EXTERNAL NAME [SqlIterators].[SqlIterators.MSForEachReplacements].[ExecuteForEachTable]

